package CCRMServer.Controllers;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import CCRMServer.DBConnect;
import Common.SERVER_COMMANDS;
import Common.ServerAnswer;
import Common.Entities.Category;
import Common.Entities.CustomerResponse;
import Common.Entities.Product;
import Common.Entities.ProductPicture;
import Common.Entities.STATUS;
import Common.Entities.Type;

public class CatalogServerManager {

	/**
	 * This method register a new category method into the DB
	 * @param cat - category to add
	 * @return Server answer with a relevant message or null in case of insertion problem 
	 */
	public static ServerAnswer registerCategory(Category cat) {
		String filter = "categoryName = '" + cat.getItmName() + "'";
		Connection conDB = DBConnect.mySQLConnection();
		try {
			// query DB
			ResultSet rs = DBConnect.selectWithFilter(conDB, "CatalogCategories", null, filter);
			// if category already exists, return null
			if (rs.next()) return null;
			// insert new record to DB
			String[] fields = {"categoryName", "categoryDescription"};
			String[] values = {cat.getItmName(), cat.getItmDescription()};
			boolean isOK = DBConnect.insertSingleRecord(conDB, "CatalogCategories", fields, values);
			if (isOK)
				return new ServerAnswer(SERVER_COMMANDS.ADD_CATEGORY, null, "Action succeeded");
			return null;
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.ADD_CATEGORY, null, "DB Problem");
		}
	}
	/**
	 * This method search for a products category in the DB
	 * @param catName - category to search
	 * @return - ResultSet with search results or null in case of DB problem
	 */
	public static ResultSet searchCategoryByName(String catName) {
		String filter = "categoryName = '" + catName + "'";
		Connection conDB = DBConnect.mySQLConnection();
		try {
			return DBConnect.selectWithFilter(conDB, "CatalogCategories", null, filter);
		} catch (SQLException e) {
			return null;
		}
	}

	/**
	 * This method returns all product types related to a specified category
	 * @param catName - a category that the searched types should be linked to
	 * @return - ResultSet of the search results or null in case of DB problem
	 */
	public static ResultSet getCategoryTypes(String catName) {
		String filter = "ctegoryName = '" + catName + "'";
		Connection conDB = DBConnect.mySQLConnection();
		try {
			return DBConnect.selectWithFilter(conDB, "catalogtypes", "typeName", filter);
		} catch (SQLException e) {
			return null;
		}
	}
	/**
	 * This method register new type record to the DB
	 * @param itmName - new type's name
	 * @param categoryName - product's category the type is associated with
	 * @return server answer with a status message
	 */
	public static ServerAnswer registerType(String itmName, String categoryName) {
		String filter = "typeName = '" + itmName + "' AND ctegoryName = '" + categoryName + "'";
		Connection conDB = DBConnect.mySQLConnection();
		try {
			ResultSet rs = DBConnect.selectWithFilter(conDB, "catalogtypes", null, filter);
			if (rs.next())
				return new ServerAnswer(SERVER_COMMANDS.ADD_TYPE, null, "Type already related to this Category");
			String fields[] = {"typeName", "ctegoryName"};
			String values[] = {itmName, categoryName};
			boolean isOK = DBConnect.insertSingleRecord(conDB, "catalogtypes", fields, values);
			if (isOK)
				return new ServerAnswer(SERVER_COMMANDS.ADD_TYPE, null, "Action succeeded");
			return new ServerAnswer(SERVER_COMMANDS.ADD_TYPE, null, "Action failed");
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.ADD_TYPE, null, "DB Problem");
		}
	}
	
	/**
	 * This method register a new product into the the DB or update an existing product
	 * @param product - product to add or to update
	 * @return server answer with a status message
	 */
	public static ServerAnswer registerProduct(SERVER_COMMANDS command, Product product) {
		Connection conDB = DBConnect.mySQLConnection();
		boolean isOK;
		String message = new String();
		try {
			// case add new product
			if (product.getProductID() == -1) {
				String insertFields[] = {"productName", "productDescription", "productBasePrice", "productStatus"};
				String insertValues[] = new String[4];
				insertValues[0] = product.getProductName();
				insertValues[1] = product.getProductDescription();
				insertValues[2] = new Float(product.getProductBasePrice()).toString();
				insertValues[3] = "1";
				isOK = DBConnect.insertSingleRecord(conDB, "products", insertFields, insertValues);
				String table = "products";
				String fields = "MAX(productID)";
				ResultSet rs = DBConnect.selectWithFilter(conDB, table, fields, null);
				String productID = new String();
				if (rs.next()) productID = rs.getString(1);
				if (isOK)
					isOK = putProductPicture(product.getPicture());
				if (isOK)
					return new ServerAnswer(command, null, "Action succeeded\n" + "New product ID: " + productID);
				return new ServerAnswer(command, null, "Action failed");	
			}
			// case update existing product
			else if (isProductExists(new Long(product.getProductID()).toString())) {
					// update product record
					String updateFields[] = {"productName", "productDescription", "productBasePrice"};
					String updateValues[] = new String[3];
					updateValues[0] = product.getProductName();
					updateValues[1] = product.getProductDescription();
					updateValues[2] = new Float(product.getProductBasePrice()).toString();
					String keyName[] = {"productID"};
					String keyVal[] = {Long.toString(product.getProductID())};
					isOK = DBConnect.updateSingleRecord(conDB, "products", updateFields, updateValues, keyName, keyVal);
					// update product status if necessary
					String table = "products";
					String fields = "productStatus";
					String filter = "productID = '" + Long.toString(product.getProductID()) + "'";
					ResultSet rs = DBConnect.selectWithFilter(conDB, table, fields, filter);
					if (rs.next())
						if (STATUS.toInt(product.getProductStatus()) != rs.getInt(1))
							message = enableDisableProduct(product.getProductID(), product.getProductStatus());
					// update product picture
					if (isOK)
						isOK = putProductPicture(product.getPicture());
					// update product - type relations
					if (isOK)
						isOK = registerProductType(product.getProductCategoriesAndTypes(), Long.toString(product.getProductID()));
					if (isOK)
						return new ServerAnswer(command, message, "Update Action succeeded");
				return new ServerAnswer(command, message, "Update Action failed");
			// case not a new product and product does not exists
			} else
				return new ServerAnswer(command, message, "Product does not exists");
		} catch (SQLException e) {
			return new ServerAnswer(command, message, "DB Problem");
		}
	}
	
	/**
	 * This method sets an updated list of product - types relations to the DB
	 * @param productCategoriesAndTypes - list of the new set of product - type relations
	 * @return true if action success otherwise return false
	 * @throws SQLException
	 */
	public static boolean registerProductType(LinkedList<Type> productCategoriesAndTypes, String productID) throws SQLException{
		String filter = "productID = '" + productID + "'";
		Connection conDB = DBConnect.mySQLConnection();
		
		// delete out-of-date set of relations
		DBConnect.DeleteRecords(conDB, "productsintypes", filter);
		
		// set updated set of relations
		String fields[] = {"typeID", "productID"};
		String values[] = new String[2];
		values[1] = productID;
		for (Type t : productCategoriesAndTypes) {
			values[0] = t.getItmID();
			boolean isOK = DBConnect.insertSingleRecord(conDB, "productsintypes", fields, values);
			if (!isOK) return false;
		}
		return true;
	}
	/**
	 * This method verify rather a specific product exists in the DB
	 * @param productID - product ID to search for
	 * @return true if product exists in the DB otherwise return false
	 * @throws SQLException
	 */
	public static boolean isProductExists (String productID) throws SQLException {
		String filter = "productID = '" + productID + "'";
		Connection conDB = DBConnect.mySQLConnection();
		ResultSet rs = DBConnect.selectWithFilter(conDB, "products", null, filter);
		if (rs.next()) return true;
		return false;
	}
	
	/**
	 * This method verify rather a specific product picture exists in the DB
	 * @param picturePath - picture's path in the file system
	 * @param pictureName - picture's file name
	 * @return true if picture exists in the DB otherwise return false
	 * @throws SQLException
	 */
	public static boolean isPictureExists(String picturePath, String pictureName) throws SQLException {
		String filter = "picturePath = '" + picturePath + "' AND pictureName = '" + pictureName + "'";
		Connection conDB = DBConnect.mySQLConnection();
		ResultSet rs = DBConnect.selectWithFilter(conDB, "productspictures", null, filter);
		if (rs.next()) return true;
		return false;
	}
	
	/**
	 * This method receives product picture as a raw bytes array, write it to a JPG file and register a new product picture to thew DB
	 * @param picture - picture raw data
	 * @return
	 */
	public static boolean putProductPicture(ProductPicture picture) {
		File picDir = new File ("Products_Pictures");
		if (!picDir.exists()) {
			try {
				picDir.mkdir();
			} catch (SecurityException e) {
				return false;
			}
		}
		Connection conDB = DBConnect.mySQLConnection();
		String pictureName = null;
		String productID = null;
		try {
			if (picture.getProductID().equals("-1")) {
				ResultSet rs = DBConnect.selectWithFilter(conDB, "products", "MAX(productID)", null);
				if (rs.next()) {
					productID = rs.getString(1);
					pictureName = productID + ".jpg";
				}
			} else {
				pictureName = picture.getProductID() + ".jpg";
				productID = picture.getProductID();
			}
			File picFile = new File (picDir, pictureName);
			FileOutputStream picOS = new FileOutputStream(picFile);
			picOS.write(picture.picture, 0, picture.picture.length);
			picOS.close();
			if (!isPictureExists(picDir.getName(), pictureName)) {
				String insFields[] = {"picturePath", "pictureName", "pictureTitle", "productID"};
				String insValues[] = {picDir.getName(), pictureName, picture.getPictureTitle(), productID};
				return DBConnect.insertSingleRecord(conDB, "productspictures", insFields, insValues);
			} else {
				String updFields[] = {"pictureTitle", "productID"};
				String updValues[] = {picture.getPictureTitle(), productID};
				String updKeyName[] = {"picturePath", "pictureName"};
				String updKeyVals[] = {picDir.getName(), pictureName};
				return DBConnect.updateSingleRecord(conDB, "productspictures", updFields, updValues, updKeyName, updKeyVals);
			}
		} catch (Exception e) {
			return false;
		}
		
			
	}
	/**
	 * This method search the DB for product according to a search string.
	 * string can be a product ID or sub-string of product name or sub-string of product description
	 * @param searchString - the search string
	 * @return - search results table (might be an empty table) or null in case of error
	 */
	public static ResultSet searchProductByString(String searchString) {
		String fields = "productID, productName, productStatus, productBasePrice, productDescription";
		String filter = "productID = '" + searchString + "' OR " +
						"productName LIKE '%" + searchString + "' OR productName LIKE '" + searchString + "%' OR productName LIKE '%" + searchString + "%' OR " +
						"productDescription LIKE '%" + searchString + "'OR productDescription LIKE '" + searchString + "%' OR productDescription LIKE '%" + searchString + "%'";
		Connection conDB = DBConnect.mySQLConnection();
		try {
			ResultSet rs = DBConnect.selectWithFilter(conDB, "products", fields, filter);
			return rs;
		} catch (SQLException e) {
			return null;
		}
	}
	
	/**
	 * This method fetch the following product data:
	 * 		- product record from Products table in the DB
	 * 		- product's related types and categories (if any)
	 * 		- product's picture (if exists)
	 * @param productID - product ID to search the DB
	 * @return - ServerAnswer with the data or status message in case of error
	 */
	public static ServerAnswer getProductByID(long productID) {
		// data fields
		String productName;
		String productDescription;
		float productBasePrice;
		int stat;
		STATUS productStatus;
		String pictureName = null;
		String pictureTitle = null;
		String picturePath = null;
		LinkedList <Type>productCategoriesAndTypes = new LinkedList<Type>();
		ProductPicture picture;
		
		try {
			// get product record
			String filter = "productID = '" + productID + "'";
			Connection conDB = DBConnect.mySQLConnection();
			ResultSet rs = DBConnect.selectWithFilter(conDB, "products", null, filter);
			// if product does not exists - return status message
			if (!rs.next())
				return new ServerAnswer(SERVER_COMMANDS.GET_PRODUCT, null, "Product does not exsits in the DB");
			
			productName = rs.getString(2);
			productDescription = rs.getString(3);
			productBasePrice = rs.getFloat(4);
			stat = rs.getInt(5);
			switch (stat) {
			case 1:
				productStatus = STATUS.ACTIVE;
				break;
			case 2:
				productStatus = STATUS.NOT_ACTIVE;
				break;
			default:
				productStatus = STATUS.SUSPENDED;
				break;
			}
			rs.close();
			
			// get product picture
			rs = DBConnect.selectWithFilter(conDB, "productspictures", null, filter);
			if (rs.next()) {
				pictureName = rs.getString(2);
				pictureTitle = rs.getString(3);
				picturePath = rs.getString(1);
			}
			rs.close();
			// locate image file on the hard-disk and prepare to send it to the client
			try {
				File picFile = new File(picturePath + "//" + pictureName);
				if (picFile.exists()) {
					byte[] picArray = new byte[(int)picFile.length()];
					FileInputStream picInStrm = new FileInputStream(picFile);
					BufferedInputStream picInBuf = new BufferedInputStream(picInStrm);
					picInBuf.read(picArray, 0, picArray.length);
					picInBuf.close();
					picture = new ProductPicture (new Long(productID).toString(), pictureTitle, picturePath, picArray.length);
					picture.setPicture(picArray);
					picture.setPictureName(pictureName);
				} else
					picture = null;
			} catch (IOException e) {
				picture = null;
			}
			
			// get product types
			String fields = "catalogtypes.typeID, catalogtypes.ctegoryName, catalogtypes.typeName";
			filter = "productsintypes.typeID = catalogtypes.typeID AND productsintypes.productID = '" + productID + "'";
			rs = DBConnect.selectWithFilter(conDB, "productsintypes, catalogtypes", fields, filter);
			while (rs.next()) {
				productCategoriesAndTypes.add(new Type (rs.getString(1), rs.getString(3), null, rs.getString(2)));
			}
		} catch (SQLException e) {
			return new ServerAnswer(SERVER_COMMANDS.GET_PRODUCT, null, "DB Problem");
		}
		// done fetching the data.
		// now packing and return to CCRMServer:
		Product product = new Product (productID, productName, productDescription,
										productBasePrice, productStatus);
		for (Type typ : productCategoriesAndTypes)
			product.addTypeToProduct(typ);
		
		product.setPicture(picture);
		
		return new ServerAnswer(SERVER_COMMANDS.GET_PRODUCT, product, "");
	}
	/**
	 * This method receives list of product types assigned to a specific product.
	 * It returns a Product object that contains only types that are not assigned to the supplied product
	 * 
	 * @param assigned - list of assigned product types
	 * @param productID - ID of the product the types in assigned list are assigned to
	 * @return - Product with types that available to be assigned to the product or null in case of error
	 */
	public static LinkedList<Type> getAvailableTypes(LinkedList<Type> assigned, String productID) {
		LinkedList<Type> diff_types = new LinkedList<Type>();
		Type typ;
		String fields = "typeID, ctegoryName, typeName";
		String from = "CatalogTypes";
		Connection conDB = DBConnect.mySQLConnection();
		try {
			// get all types assigned to other products
			ResultSet rs = DBConnect.selectWithFilter(conDB, from, fields, null);
			// Differ between assigned types and query results
			while (rs.next()) {
				boolean contain = false;
				typ = new Type (rs.getString(1), rs.getString(3), null, rs.getString(2));
				for (int i = 0 ; i < assigned.size() ; i ++)
					if (assigned.get(i).getItmID().equals(typ.getItmID())) {
						contain = true;
						i = assigned.size();
					}
				if (!contain)
					diff_types.add(typ);
			}
			return diff_types;
		} catch (SQLException e) {
			return null;
		}
	}
	/**
	 * This method change the status of a product in the DB.
	 * if product is not active or suspended the method will set a new status
	 * if the product is active, the method will set a new status if and only if the product is not related to unfinished sales
	 * @param productID - product ID
	 * @param newStatus - new status value
	 * @return - action status message
	 * @throws SQLException
	 */
	public static String enableDisableProduct(long productID, STATUS newStatus) throws SQLException{
		// check if product exists and his status
		String table = "products";
		String field = "productStatus";
		String filter = "productID = '" + Long.toString(productID) + "'";
		Connection conDB = DBConnect.mySQLConnection();

		ResultSet rs = DBConnect.selectWithFilter(conDB, table, field, filter);
		if (rs.next()) {
			int current = rs.getInt(1);
			rs.close();
			// case de-activating product
			if (current == 1) {
				// check if product is in unfinished sales
				table = "sales AS s, marketingtemplates AS MT";
				field = "COUNT(S.saleID)";
				filter = "MT.productID = '" + Long.toString(productID) + "' AND S.templateID = MT.templateID AND S.saleFinishDate >= CURDATE()";
				rs = DBConnect.selectWithFilter(conDB, table, field, filter);
				if (rs.next() && rs.getInt(1) > 0)
					return "Unable to set status to a product related to " + rs.getString(1) + " unfinished sale(s).";
			}
			// setting new status
			table = "products";
			String fields[] = {"productStatus"};
			String[] values = new String[1];
			values[0] = Integer.toString(STATUS.toInt(newStatus));
			String keyName[] = {"productID"};
			String keyVal[] = {Long.toString(productID)};
			boolean isOK = DBConnect.updateSingleRecord(conDB, table, fields, values, keyName, keyVal);
			if (isOK)
				return "Product status has been set";
		}
		return "Product does not exists";
	}
	
	public static ServerAnswer showProductInSale(CustomerResponse response){
		Connection conDB = DBConnect.mySQLConnection();
		try {
			ResultSet res = DBConnect.selectWithFilter(conDB, "products AS p, marktingtemplats AS mt, sales AS s", "p.productID, p.productName, p.productBasePrice, s.saleDescription",
			           "s.SaleID = " + response.getSaleID() + " AND s.templateID = mt.templateID AND p.productID = mt.productID");
			String[] productData = new String[3];
			String saleDescription = ""; 
			while(res.next()){
				productData[0] = String.valueOf(res.getInt("productID"));
				productData[1] = res.getString("productName");
				productData[2] = String.valueOf(res.getFloat("productBasePrice"));
				saleDescription = res.getString("saleDescription");
			}
			Object[] data = new Object[3];
			data[0] = response;
			data[1] = productData;
			data[2] = saleDescription;
			return new ServerAnswer(SERVER_COMMANDS.SHOW_PRODUCT_IN_SALE, data);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return new ServerAnswer(SERVER_COMMANDS.SHOW_PRODUCT_IN_SALE, null, e.getMessage());
		}
	}
}